SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[GtMoodysChange]
	(@Rating1 VARCHAR(50) , 
	@Rating2 VARCHAR(50) )
	returns INT 

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	
	DECLARE @Return INT 
    -- Insert statements for procedure h
	DECLARE @RatingVal1 INT, @RatingVal2 INT
    
	DECLARE @RefTable TABLE(
		Rating VARCHAR(10),
		VALUE INT
        )
	INSERT INTO @RefTable
	        ( Rating, VALUE )
	Select 'Baa1', -- Rating - varchar(10)
	          1  -- VALUE - int
	UNION ALL
	SELECT 'Baa2', 2
	UNION ALL
	SELECT 'Baa3', 3
	UNION ALL
	SELECT 'Ba1', 4
	UNION ALL	
	SELECT 'Ba2', 5
	UNION ALL
	SELECT 'Ba3', 6
		UNION ALL
	SELECT 'B1', 7
		UNION ALL	
	SELECT 'B2', 8
		UNION ALL
	SELECT 'B3', 9	
	UNION ALL
	Select 'Caa1', 10
		UNION ALL
	SELECT 'Caa2', 11
		UNION ALL
	SELECT 'Caa3', 12
		UNION all
	SELECT 'NR' , 100

	 SET @RatingVal1 = (SELECT  VALUE FROM @refTable rt WHERE rt.rating = @Rating1);
	 SET @RatingVal2   =(select VALUE FROM @refTable rt WHERE rt.rating = @Rating2);
		 
	set @return =  CASE WHEN @RatingVal1 > @ratingVal2 THEN -1
				WHEN @RatingVal1 =@ratingVal2 THEN 0
				WHEN @RatingVal1 <@ratingVal2 THEN 1
				ELSE 4
			END          
  RETURN @RETURN       
END ;
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Generic', 'SCHEMA', N'dbo', 'FUNCTION', N'GtMoodysChange', NULL, NULL
GO
